﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace PageAdmin.Utils.ORM
{
    /// <summary>
    /// 映射到dynamic相关的查询扩展方法
    /// </summary>
    public static class QueryToDynamic
    {
        /// <summary>
        /// 根据sql语句查询，返回第一条数据的dynamic类型映射
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="sql">执行的sql语句</param>
        /// <returns></returns>
        public static dynamic SqlQueryFirst(this DataBaseContext dbContext, string sql)
        {
            dynamic result =dbContext.DbConnection.QueryFirstOrDefault(sql, dbContext.DbParamterList, dbContext.DbTransaction, dbContext.CommandTimeout);
            dbContext.ExecutedHandel();
            return result;
        }

        /// <summary>
        /// 根据sql语句查询，返回dynamic类型的IEnumerable
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="sql">执行的sql语句</param>
        /// <returns></returns>
        public static IEnumerable<dynamic> SqlQuery(this DataBaseContext dbContext, string sql)
        {
            IEnumerable<dynamic> result = dbContext.DbConnection.Query(sql, dbContext.DbParamterList, dbContext.DbTransaction,true,dbContext.CommandTimeout);
            dbContext.ExecutedHandel();
            return result;
        }

        /// <summary>
        /// 根据参数查询，查询第一条数据的dynamic映射，查询不到则返回null
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">要查询的表</param>
        /// <param name="fields">要查询的字段</param>
        /// <param name="sqlCondition">查询条件,不需要加where</param>
        /// <param name="sqlOrderBy">排序，不需要加order by,如：id asc</param>
        /// <returns></returns>
        public static dynamic QueryFirst(this DataBaseContext dbContext, string table, string fields = null, string sqlCondition = null, string sqlOrderBy = null)
        {
            if (string.IsNullOrEmpty(fields))
            {
                fields = "*";
            }
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + schmea;
            }
            if (fields == "*")
            {
                fields = string.Join(",", dbContext.GetTableFields(table, true));
            }
            string sql = BuildSqlHelper.CreateSelectSql(table, fields, sqlCondition, sqlOrderBy,1);
            return dbContext.SqlQueryFirst(sql);
        }

        /// <summary>
        /// 根据参数查询，返回dynamic类型的IEnumerable
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">表名</param>
        /// <param name="fields">要查询的字段</param>
        /// <param name="sqlCondition">查询条件</param>
        /// <param name="sqlOrderBy">排序，不需要加order by,如：id asc</param>
        /// <param name="topNum">显示几条数据，0则列出所有数据</param>
        /// <returns></returns>
        public static IEnumerable<dynamic> Query(this DataBaseContext dbContext, string table, string fields = null, string sqlCondition = null, string sqlOrderBy = null, int topNum = 0)
        {
            if (string.IsNullOrEmpty(fields))
            {
                fields = "*";
            }
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + schmea;
            }
            if (fields == "*")
            {
                fields = string.Join(",", dbContext.GetTableFields(table, true));
            }
            string sql = BuildSqlHelper.CreateSelectSql(table, fields, sqlCondition, sqlOrderBy, topNum);
            return dbContext.SqlQuery(sql);
        }

        /// <summary>
        /// 从缓存中查询，返回dynamic类型的IEnumerable
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">查询的表名</param>
        /// <param name="conditionFunc">条件委托</param>
        /// <returns></returns>
        public static IEnumerable<dynamic> CacheQuery(this DataBaseContext dbContext, string table, Func<dynamic, bool> conditionFunc = null)
        {
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + schmea;
            }
            string key = table + "_dynamic_cache";
            IEnumerable<dynamic> dataList = CacheHelper.Get<IEnumerable<dynamic>>(key);
            string fields = string.Join(",", dbContext.GetTableFields(table, true));
            if (dataList == null)
            {
                string sql= BuildSqlHelper.CreateSelectSql(table, fields);
                dataList = dbContext.SqlQuery(sql).ToList();
                double cacheMinutes = dbContext.CacheMinutes;
                if (cacheMinutes > 0)
                {
                    CacheHelper.Add(key, dataList, cacheMinutes);
                }
            }
            if (conditionFunc != null)
            {
                dataList = dataList.Where<dynamic>(conditionFunc);
            }
            return dataList;
        }

        /// <summary>
        /// 根据参数查询分页数据，返回对应页码的的dynamic类型的IEnumerable
        /// </summary>
        /// <param name="dbContext">DataBaseContext对象</param>
        /// <param name="table">要查询的表</param>
        /// <param name="fields">查询字段</param>
        /// <param name="condition">查询条件,不需要加where</param>
        /// <param name="sqlOrderBy">排序，不需要加order by,如：id asc</param>
        /// <param name="pageInfo">分页信息类</param>
        /// <returns></returns>
        public static IEnumerable<dynamic> Query(this DataBaseContext dbContext, string table, string fields, string condition, string sqlOrderBy, PageInfo pageInfo)
        {
            if (string.IsNullOrEmpty(fields))
            {
                fields = "*";
            }
            string schmea = dbContext.Schema;
            if (!string.IsNullOrEmpty(schmea))
            {
                table = schmea + "." + schmea;
            }
            if (fields=="*")
            {
                fields = string.Join(",",dbContext.GetTableFields(table,true));
            }
            string sql = BuildSqlHelper.CreateSelectSql(table, "count(1) as co", condition, null);
            int recordCount = (int)dbContext.DbConnection.ExecuteScalar<int>(sql, dbContext.DbParamterList, dbContext.DbTransaction, dbContext.CommandTimeout);
            int pageSize = pageInfo.PageSize;
            int pageCount = (recordCount % pageSize == 0) ? (recordCount / pageSize) : (recordCount / pageSize + 1);
            pageInfo.RecordCount = recordCount;
            pageInfo.PageCount = pageCount;
            if (pageInfo.CurrentPage > pageCount)
            {
                pageInfo.CurrentPage = pageCount;
            }
            sql = BuildSqlHelper.CreateSelectSql(table, fields, condition, sqlOrderBy, pageInfo);
            IEnumerable<dynamic> result=dbContext.DbConnection.Query(sql, dbContext.DbParamterList, dbContext.DbTransaction,true, dbContext.CommandTimeout);
            //result = DBConvertHelper.DynamicListFirstCharUpper(result);
            dbContext.ExecutedHandel();
            return result;
        }


    }
}
